package com.authine.cloudpivot.ext.utils;

import com.authine.cloudpivot.ext.dto.DemandGoodDto;
import com.authine.cloudpivot.ext.dto.OrderCustomerGoodsDto;
import com.authine.cloudpivot.ext.dto.OrderVendorGoodDto;
import com.authine.cloudpivot.ext.dto.QuotationGoodDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class JdbcUtil {
    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);

    public static final String CUSTOMER_ORDER_SQL = "SELECT\n" +
            "\totcg.id,\n" +
            "\toitc.id orderCustomerId,\n" +
            "\toitc.order_type orderType,\n" +
            "\toitc.order_type orderCustomerId,\n" +
            "\tGROUP_CONCAT( oitt.createdTime ) traceCreateTime,\n" +
            "\tGROUP_CONCAT( oitt.logistics ) logistics,\n" +
            "\tGROUP_CONCAT( oitt.waybill ) waybill,\n" +
            "\tGROUP_CONCAT( oitt.arrival_time ) arrival_time,\n" +
            "\tGROUP_CONCAT( oitt.customer_organization_id) customer_organization_id,\n" +
            "\totcg.order_customer_id,\n" +
            "\totcg.order_customer_goods_status,\n" +
            "\ttimestampdiff(\n" +
            "\t\tHOUR,\n" +
            "\t\totcg.modifiedTime,\n" +
            "\tnow()) overTimeHour,\n" +
            "\totcg.vendor_organization_id,\n" +
            "\totcg.NAME,\n" +
            "\totcg.brand,\n" +
            "\totcg.model,\n" +
            "\totcg.spec,\n" +
            "\totcg.delivery,\n" +
            "\totcg.createdTime \n" +
            "FROM\n" +
            "\tordercenter.i_ordercenter_t_order_customer_goods otcg\n" +
            "\tLEFT JOIN ordercenter.i_ordercenter_t_order_trace oitt ON otcg.id = oitt.order_customer_goods_id \n" +
            "\tAND oitt.del = 0\n" +
            "\tLEFT JOIN ordercenter.i_ordercenter_t_order_customer oitc ON otcg.order_customer_id = oitc.id \n" +
            "\tAND oitc.del = 0\n" +
            "\tLEFT JOIN ordercenter.i_ordercenter_t_order_goods_relation itgr ON itgr.id = otcg.relation_id \n" +
            "\tAND itgr.del = 0 ";
    public static Integer getCountSql (OrderVendorGoodDto good){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append("\t itvg.id \n") ;
        sql = getCommonOrderSql(good, sql);
        getOrderGroupSql(sql);
        log.info("sql:{}",sql);
        return jdbcTemplate.queryForList(sql.toString()).size();
    }

    public static List<Map<String,Object>> getOrderList (OrderVendorGoodDto good,Integer limit ,Integer size){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("\titvg.document goodDocument,\n" +
                "\tiotv.document as order_vendor_document,\n" +
                "\tiotc.document customDocument ,\n" +
                "\tiotv.organization_name vendor_organization_name, " +
                "\tiotc.organization_name,\n" +
                "\tiotv.id order_vendor_id,\n" +
                "\tiotc.id order_customer_id,\n" +
                "\tGROUP_CONCAT(JSON_UNQUOTE(json_extract(itgu.member_id,'$.name'))) grantUserMember,\n" +
                "\titvg.order_vendor_goods_status,\n" +
                "\titvg.brand,\n" +
                "\titvg.name,\n" +
                "\titvg.model,\n" +
                "\titvg.spec,\n" +
                "\titvg.quantity,\n" +
                "\titvg.unit,\n" +
                "\titvg.createdTime ,\n" +
                "\titvg.id\n" );

        sql = getCommonOrderSql(good, sql);

        sql = getOrderGroupSql(sql);
        if (limit != null && size != null){
            sql.append( " limit "+limit +","+size);
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;
    }

    public static StringBuffer getOrderGroupSql(StringBuffer sql){
        sql.append ( "\t group by itvg.vendor_organization_id,itvg.id");
        return  sql;
    }

    /**
     * 分组逻辑
     * @param sql 待拼接sql
     * @return 分组后的查询sql
     */
    public static StringBuffer getQuotationGoodGroupSql(StringBuffer sql){
        sql.append ( "\t group by istg.vendor_organization_id,istg.id");
        return  sql;
    }

    private static StringBuffer getCommonOrderSql(OrderVendorGoodDto good, StringBuffer sql) {
        sql.append ("\t FROM ordercenter.i_ordercenter_t_order_vendor_goods itvg\n" +
            "\tLEFT JOIN operation1center.i_operation1center_t_platform_grant_user itgu ON itvg.vendor_organization_id = itgu.organization_id\n" +
            "\tLEFT JOIN ordercenter.i_ordercenter_t_order_vendor iotv ON iotv.id = itvg.order_vendor_id \n" +
            "\tLEFT JOIN ordercenter.i_ordercenter_t_order_customer iotc ON iotc.id = itvg.order_customer_id \n" +
            "WHERE 1=1\n") ;
        if (good.getCustomer_organization_id()!= null && StringUtils.isNotBlank(good.getCustomer_organization_id())){
            sql.append (" and itgu.organization_id = '"+ good.getCustomer_organization_id()+"'");
        }
        if (StringUtils.isNotBlank(good.getOrder_vendor_document())){
            sql.append (" and iotv.id like '%"+ good.getOrder_vendor_document()+"%'");
        }
        if (StringUtils.isNotBlank(good.getCustomDocument())){
            sql.append (" and iotc.id like'%"+ good.getCustomDocument()+"%'");
        }
        if (good.getCustomerOwner() != null && StringUtils.isNotBlank(good.getCustomerOwner())){
            sql.append (" and itgu.member_id like  '%"+ good.getCustomerOwner()+"%'");
        }
        if (StringUtils.isNotBlank(good.getCustomDocument())){
            sql.append (" and iotc.id like  '%"+ good.getCustomDocument()+"%'");
        }
        if (StringUtils.isNotBlank(good.getCustomerOwner())){
            sql.append (" and iotc.member_name like  '%"+ good.getCustomerOwner()+"%'");
        }
        if (StringUtils.isNotBlank(good.getBrand())){
            sql.append (" and itvg.brand like  '%"+ good.getBrand()+"%'");
        }
        if (StringUtils.isNotBlank(good.getName())){
            sql.append (" and itvg.name like  '%"+ good.getName()+"%'");
        }
        if (StringUtils.isNotBlank(good.getModel())){
            sql.append (" and itvg.model like  '%"+ good.getModel()+"%'");
        }
        if (StringUtils.isNotBlank(good.getSpec())){
            sql.append (" and itvg.spec like  '%"+ good.getSpec()+"%'");
        }
        if (StringUtils.isNotBlank(good.getOrder_vendor_goods_status())){
            sql.append (" and itvg.order_vendor_goods_status =  '"+ good.getOrder_vendor_goods_status()+"'");
        }
        if (StringUtils.isNotBlank(good.getPlace_order_time_start())){
            sql.append(" and itvg.createdTime >= '" + good.getPlace_order_time_start() + "' ");
        }
        if (StringUtils.isNotBlank(good.getPlace_order_time_end())){
            sql.append(" and itvg.createdTime <= '" + good.getPlace_order_time_end() + "' ");
        }

        return  sql;
    }


    public static int getQuotationGoodCountSql(QuotationGoodDto good) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append("\t istg.id \n") ;
        sql = getQuotationGoodCommonSql(good, sql);

        getQuotationGoodGroupSql(sql);
        return jdbcTemplate.queryForList(sql.toString()).size();
    }

    private static StringBuffer getQuotationGoodCommonSql(QuotationGoodDto good, StringBuffer sql) {
        sql.append ("\t from sourcingcenter.i_sourcingcenter_t_quotation_goods istg \n" +
                "LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods itig on istg.inquiry_goods_id =itig.id\n" +
                "LEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry iti on iti.id =itig.inquiry_id\n" +
                "LEFT JOIN operation1center.i_operation1center_t_platform_grant_user itgu on  istg.vendor_organization_id = itgu.organization_id") ;
        sql.append(" where 1=1 ");
        if (good.getCustomer_organization_id()!= null){
            sql.append (" and itgu.organization_id = '"+ good.getCustomer_organization_id().getId()+"'");
        }
        if (good.getCustomerOwner() != null && StringUtils.isNotBlank(good.getCustomerOwner().getId())){
            sql.append (" and itgu.member_id like  '%"+ good.getCustomerOwner().getId()+"%'");
        }
        if (StringUtils.isNotBlank(good.getQuotation_goods_status())){
            sql.append( "and istg.quotation_goods_status"+good.getQuotation_goods_status());
        }
        return  sql;
    }

    private static StringBuffer getDemandGoodCommonSql(DemandGoodDto good, StringBuffer sql) {
        sql.append ("\t from demandpool.i_demandpool_t_demand_goods ditg left join demandpool.i_demandpool_t_demand_pool ditp on ditg.demand_pool_id= ditp.id\n" +
                "where 1=1 and ditp.demand_pool_status in (3,4,5) and ditg.demand_goods_status=1") ;
        return  sql;
    }

    public static List<Map<String, Object>> getOrderWuliuInfo(String sql){
        return jdbcTemplate.queryForList(sql, new HashMap<>());
    }

    public static String getOrderWuliuSQL(String tableName, OrderCustomerGoodsDto dto){
        StringBuilder listSql = new StringBuilder();
        listSql.append("select * from " + tableName + " where deleted='0' and order_customer_goods_id ='" + dto.getId() + "' order by createdTime desc limit "
                + (dto.getPageIndex() - 1) * dto.getPageSize() + ", " + dto.getPageSize());
        return listSql.toString();
    }


    public static List<Map<String, Object>> getQuotationGoodList(QuotationGoodDto good, Integer limit, Integer size) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("istg.document, \n" +
                "itig.quote_count, \n" +
                "itig.partake_count, \n" +
                "itig.id inquiry_good_id, \n" +
                "iti.name inquiry_name, \n" +
                "iti.id inquiry_id, \n" +
                "istg.organization_company_name, \n" +
                "istg.vendor_organization_name, \n" +
                "istg.vendor_department_name, \n" +
                "GROUP_CONCAT( itgu.member_name ) customerOwner, \n" +
                "istg.platform_category_name ,\n" +
                "iti.document inquiry_document,\n" +
                "itig.brand ,\n" +
                "itig.name name, \n" +
                "itig.model,\n" +
                "itig.spec ,\n" +
                "itig.num ,\n" +
                "itig.unit ,\n" +
                "istg.price, \n" +
                "istg.freight, \n" +
                "istg.amount, \n" +
                "istg.delivery, \n" +
                "case istg.auto_quotation\n" +
                "when '0' THEN '手动报价'\n" +
                "when '1' THEN '自动报价'\n" +
                "ELSE '其他' END as auto_quotation, \n" +
                "istg.address, \n" +
                "iti.expire,\n" +
                "istg.createdTime, \n" +
                "case istg.quotation_goods_status\n" +
                "when '0' THEN '新建'\n" +
                "when '1' THEN '审核中'\n" +
                "when '2' THEN '已退回'\n" +
                "when '3' THEN '已报价'\n" +
                "when '4' THEN '已还价'\n" +
                "when '5' THEN '已定标'\n" +
                "when '6' THEN '已关闭'\n" +
                "when '7' THEN '已终止'\n" +
                "ELSE '' END as quotation_goods_status,\n" +
                "case itig.inquiry_goods_status\n" +
                "when '0' THEN '新建'\n" +
                "when '1' THEN '审核中'\n" +
                "when '2' THEN '已退回'\n" +
                "when '3' THEN '报价中'\n" +
                "when '4' THEN '已结束'\n" +
                "when '5' THEN '已关闭'\n" +
                "ELSE '' END as inquiry_goods_status,\n" +
                "CASE istg.clarify_status\n" +
                "WHEN '0' THEN '未澄清'\n" +
                "WHEN '1' THEN '驻场待澄清'\n" +
                "WHEN '2' THEN '供应商待澄清'\n" +
                "ELSE '其他' END as clarify_status,\n" +
                "itig.clarify_record " );

        sql = getQuotationGoodCommonSql(good, sql);
        //分组
        getQuotationGoodGroupSql(sql);
        if (limit != null && size != null){
            sql.append( " limit "+limit +","+size);
        }
        log.info("sql:{}",sql);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;
    }

    public static List<Map<String, Object>> queryDemandGoodList(DemandGoodDto demandGoodDto) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append("ditg.id" );
        sql = getDemandGoodCommonSql(demandGoodDto, sql);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;

    }

    public static int getRceiveGoodCount(OrderCustomerGoodsDto good) {
        StringBuffer sb = packageCommonCondition(good);

        //分页
        if (good.getPageIndex() != null && good.getPageSize() != null){
            sb.append( " limit "+good.getPageIndex() +","+good.getPageSize());
        }
        List<Integer> list = jdbcTemplate.queryForList(sb.toString(), Integer.class);
        return  list == null ? 0 : list.size();
    }

    private static StringBuffer packageCommonCondition(OrderCustomerGoodsDto good) {
        StringBuffer sb = new StringBuffer();

        sb.append(CUSTOMER_ORDER_SQL);
        //封装查询条件
        packageSelectCondition(good,sb);

        sb.append(" GROUP BY otcg.id");
        return sb;
    }

    private static StringBuffer packageSelectCondition(OrderCustomerGoodsDto good, StringBuffer sb) {
        //封装查询条件订单商品序号
        if(StringUtils.isNotBlank(good.getId())){
            sb.append(" and otcg.id='"+good.getId()+"'");
        }
        if(StringUtils.isNotBlank(good.getOrderCustomerId())){
            sb.append(" and oitc.id='"+good.getOrderCustomerId()+"'");
        }
        if(StringUtils.isNotBlank(good.getOrder_customer_goods_status())){
            sb.append(" and otcg.order_customer_goods_status='"+good.getOrder_customer_goods_status()+"'");
        }
        if(StringUtils.isNotBlank(good.getVendorOrganizationName())){
            sb.append(" and otcg.vendor_organization_name like  '%"+ good.getVendorOrganizationName()+"%'");
        }
        if(StringUtils.isNotBlank(good.getName())){
            sb.append(" and otcg.name like  '%"+ good.getName()+"%'");
        }
        if(StringUtils.isNotBlank(good.getBrand())){
            sb.append(" and otcg.brand like  '%"+ good.getBrand()+"%'");
        }
        if(StringUtils.isNotBlank(good.getModel())){
            sb.append(" and otcg.model like  '%"+ good.getModel()+"%'");
        }
        if(StringUtils.isNotBlank(good.getSpec())){
            sb.append(" and otcg.spec like  '%"+ good.getSpec()+"%'");
        }
        if(StringUtils.isNotBlank(good.getDelivery())){
            sb.append(" and otcg.delivery like  '%"+ good.getDelivery()+"%'");
        }
        if(StringUtils.isNotBlank(good.getDelivery())){
            sb.append(" and otcg.delivery like  '%"+ good.getDelivery()+"%'");
        }
        if(StringUtils.isNotBlank(good.getModifiedTimeStart())){
            sb.append(" and otcg.modifiedTime >='"+ good.getModifiedTimeStart()+"'");
        }
        if(StringUtils.isNotBlank(good.getModifiedTimeEnd())){
            sb.append(" and otcg.modifiedTime <='"+ good.getModifiedTimeEnd() +"'");
        }
        return sb;
    }

    public static List<Map<String, Object>> getReceiveGoodList(OrderCustomerGoodsDto good, Integer start, Integer pageSize) {
        StringBuffer sb = packageCommonCondition(good);

        if (start != null && pageSize != null){
            sb.append( " limit "+start +","+pageSize);
        }
        return jdbcTemplate.queryForList(sb.toString());
    }
}
